package com.bbcare.followup.plat.service.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Service;

import com.bbcare.comm.DateUtil;
import com.bbcare.followup.plat.service.IReserveService;
import com.bbcare.followup.plat.vo.reserve.*;


/**
 * 预约模块。
 *
 * @author michael.
 * @since 2023/04.
 */
@Service
public class ReserveService implements IReserveService {
    private final static Logger logger = Logger.getLogger(ReserveService.class);
    private final static Integer TWO_WEEK   = 14;

    @Autowired
    private JdbcTemplate jdbc;

    //治疗仪时段表。
    private List<String> machineDurationList    = Arrays.asList(
            "08:00-08:20",
            "08:30-08:50",
            "09:00-09:20",
            "09:30-09:50",
            "10:00-10:20",
            "10:30-10:50",
            "11:00-11:20",
            "11:30-11:50",

            "14:00-14:20",
            "14:30-14:50",
            "15:00-15:20",
            "15:30-15:50",
            "16:00-16:20",
            "16:30-16:50"
    );


    //运动（作业）项目时段表。
    private Map<String, String > prj2AmMap = new HashMap<String, String>() {
        {
            put("08:00","08:00-08:30");
            put("08:45","08:45-09:15");
            put("09:30","09:30-10:00");
            put("10:15","10:15-10:45");
            put("11:00","11:00-11:30");
        }
    };
    private Map<String, String > prj2PmMap = new HashMap<String, String>() {
        {
            put("14:00","14:00-14:30");
            put("14:45","14:45-15:15");
            put("15:30","15:30-16:00");
            put("16:15","16:15-16:45");
        }
    };


    /**
     * 插入所有治疗仪的当日空预约。
     * @param machinePrjId
     * @param day
     */
    private void insertDailyReservation(Integer machinePrjId, String day)   {
        final int segmentSize   = Reservation.MACHINE_TIME_BEGIN.length;
        jdbc.batchUpdate("INSERT INTO rsre_reservation (prj_id_ref,cure_date,cure_begintime,cure_endtime) values (?,?,?,?) ",
                new BatchPreparedStatementSetter(){
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        ps.setInt(1, machinePrjId);
                        ps.setDate(2, java.sql.Date.valueOf(day));
                        ps.setTime(3, java.sql.Time.valueOf(Reservation.MACHINE_TIME_BEGIN[i]+":00"));
                        ps.setTime(4, java.sql.Time.valueOf(Reservation.MACHINE_TIME_END[i]+":00"));
                    }
                    @Override
                    public int getBatchSize() {
                        return segmentSize;
                    }
        });
    }

    /**
     *   生成治疗仪的2周的空预约。
     */
    public void generate2WeekMachineReservation()    {
        logger.info("++++generate2WeekMachineReservation() begin...");

        try {
            Date date = new Date();
            date    = DateUtil.addDay(date, 1);
            String dateStr = DateUtil.getDate_8(date);
            String querySql;
            Integer count;
            for (int i = 0; i != TWO_WEEK+1; i++) {
                querySql = "SELECT COUNT(*) FROM rsre_reservation WHERE cure_date='" + dateStr + "'";
                count = jdbc.queryForObject(querySql, Integer.class);
                if (0 == count.intValue()) {
                    //当日的空预约尚无。
                    insertDailyReservation(Reservation.MACHINE_NXH, dateStr);
                    insertDailyReservation(Reservation.MACHINE_SJD, dateStr);
                    insertDailyReservation(Reservation.MACHINE_JRJ, dateStr);
                } else {
                    //已有空预约了。
                }
                date    = DateUtil.addDay(date, 1);
                dateStr = DateUtil.getDate_8(date);
            }
        } catch (java.text.ParseException pe)   {
            logger.error(pe);
        }

        logger.info("++++generate2WeekMachineReservation() end.");
    }

    /**
     * 查询所有的治疗师。
     */
    public List<Map<String,Object>> queryAllCurer() {
        final String querySql   = "SELECT * FROM rsre_curer";
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        return rowList;
    }

    /**
     * 是否周六日。
     * @param date
     * @return
     */
    private boolean isSatOrSunday(final java.sql.Date date, final String dateStr) {
        if (dateStr.indexOf("01-01")>0 || dateStr.indexOf("05-01")>0 || dateStr.indexOf("10-01")>0) {
            return true;
        }

        Calendar cal   = Calendar.getInstance();
        cal.setTime(date);
        int i = cal.get(Calendar.DAY_OF_WEEK);
        int saturday = Calendar.SATURDAY;
        int sunday = Calendar.SUNDAY;
        if (i == saturday || i == sunday)   {
            return true;
        }
        return false;
    }

    private void insertCurerSchedule(List<Integer> curerIdList, String dateStr)  {
        logger.info("++++insertCurerSchedule() number of curer is:"+curerIdList.size()+", work_date:"+dateStr);
        jdbc.batchUpdate("INSERT INTO rsre_curer_schedule (curer_id_ref, work_date, am, pm) values (?,?,?,?) ",
                new BatchPreparedStatementSetter(){
                    @Override
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        final java.sql.Date date = java.sql.Date.valueOf(dateStr);
                        int col = 1;
                        ps.setInt(col++, curerIdList.get(i));
                        ps.setDate(col++, date);
                        if (!isSatOrSunday(date, dateStr)) {
                            ps.setString(col++, "Y");
                            ps.setString(col++, "Y");
                        } else {
                            //周六日缺省是休假的。
                            ps.setString(col++, "N");
                            ps.setString(col++, "N");
                        }
                    }
                    @Override
                    public int getBatchSize() {
                        return curerIdList.size();
                    }
                });
    }

    /**
     * 生成2周的治疗师排班。
     */
    public void generateCurerSchedule() {
        try {
            Date date = new Date();
            date = DateUtil.addDay(date, 1);
            String dateStr = DateUtil.getDate_8(date);
            String querySql;
            Integer count,curerId;
            List<Map<String,Object>> curerList  = queryAllCurer();
            List<Integer> curerIdList   = new ArrayList<>();
            for(Map<String,Object> curerMap : curerList)   {
                curerId = (Integer)curerMap.get("id");
                curerIdList.add(curerId);
            }
            for (int i = 0; i != TWO_WEEK+1; i++) {
                querySql = "SELECT COUNT(*) FROM rsre_curer_schedule WHERE work_date='" + dateStr + "'";
                logger.info("++++generateCurerSchedule() querySql:"+querySql);
                count = jdbc.queryForObject(querySql, Integer.class);
                if (0 == count.intValue()) {
                    //尚无当日的空排班记录。
                    insertCurerSchedule(curerIdList, dateStr);
                } else {
                    //已有排班记录了。
                }
                date = DateUtil.addDay(date, 1);
                dateStr = DateUtil.getDate_8(date);
            }
            logger.info("++++generateCurerSchedule() dateStr:"+dateStr);
        } catch (java.text.ParseException pe)   {
            logger.error(pe);
        }
    }


    private Week2Reservation getMachineReservation(String querySql) {
        Long xqj;
        Integer machineId,reservationId;
        Week2Reservation weekReservation = new Week2Reservation();
        Reservation reservation;
        DailyReservation dailyReservation;
        String machineName,cureDate,cureBeginTime,cureEndTime;
        List<Map<String,Object>> rowList = jdbc.queryForList(querySql);
        for(Map rowMap : rowList)   {
            reservationId   = (Integer)rowMap.get("id");
            machineId   = (Integer)rowMap.get("prj_id_ref");
            xqj = (Long)rowMap.get("xqj");
            machineName = rowMap.get("project_name").toString();
            cureDate    = rowMap.get("cure_date").toString();
            cureBeginTime   = rowMap.get("cure_begintime").toString();
            cureEndTime = rowMap.get("cure_endtime").toString();

            reservation = new Reservation();
            reservation.setId(reservationId);
            reservation.setStartTime(cureBeginTime);
            reservation.setEndTime(cureEndTime);
            dailyReservation    = weekReservation.getDailyByDate(cureDate);
            if (null == dailyReservation)   {
                dailyReservation    = new DailyReservation();
                dailyReservation.setDate(cureDate);
                switch (xqj.intValue()) {
                    case 1:
                        dailyReservation.setXingqi("星期日");
                        break;
                    case 2:
                        dailyReservation.setXingqi("星期一");
                        break;
                    case 3:
                        dailyReservation.setXingqi("星期二");
                        break;
                    case 4:
                        dailyReservation.setXingqi("星期三");
                        break;
                    case 5:
                        dailyReservation.setXingqi("星期四");
                        break;
                    case 6:
                        dailyReservation.setXingqi("星期五");
                        break;
                    case 7:
                        dailyReservation.setXingqi("星期六");
                        break;
                    default:
                        dailyReservation.setXingqi("星期六");
                }
                weekReservation.addDailyReservation(dailyReservation);
            }
            dailyReservation.addReservation(reservation);
            weekReservation.setMachineIdRef(machineId);
            weekReservation.setMachineName(machineName);
        }

        return weekReservation;
    }

    /**
     * 查询可用于预约的治疗仪时段。
     * @return
     */
    @Override
    public List<Week2Reservation> queryAvailableReservation()   {
        final Integer[] machineIds  = {6,7,8};
        final String sqlTemplate  = "SELECT a.*, b.project_name, DAYOFWEEK(a.cure_date) AS xqj " +
                "FROM rsre_reservation a LEFT JOIN rsre_project b ON a.prj_id_ref=b.id " +
                "WHERE prj_id_ref=%MACHINE_ID% AND cure_date>now() " +
                "ORDER BY a.prj_id_ref, a.cure_date, a.cure_begintime";
        String querySql;
        List<Week2Reservation> retList  = new ArrayList<>();
        for(Integer machineId : machineIds )   {
            querySql    = sqlTemplate.replaceAll("%MACHINE_ID%", machineId.toString());
            logger.info("++++queryAvailableReservation() querySql:"+querySql);
            retList.add(getMachineReservation(querySql));
        }

        return retList;
    }

    /**
     * 治疗仪下约。
     * @param reservationId 预约ID。
     * @param patientId 患者ID。
     * @param accountId 下约人ID（登录ID）。
     * @return
     */
    public Map makeMachineReservation(final Integer reservationId, final String patientId, final String patientName,final String accountId)    {
        Map<String,Object> retMap   = new HashMap<>();
        //查询该空闲预约是否已被预约掉了。
        final String querySql   = "SELECT b.user_name, a.account_id_ref " +
                "FROM rsre_reservation a LEFT JOIN pt_user_info b ON b.id=a.patient_id_ref " +
                "WHERE b.tenant_id!='0' AND a.id=" + reservationId.toString();
        final String updateSql  = "UPDATE rsre_reservation SET patient_id_ref='" + patientId.toString() +"', account_id_ref='" + accountId.toString() + "' WHERE id="+reservationId.toString();
        logger.info("++++makeMachineReservation() querySql:"+querySql);
        boolean idle    = false;
        String reservedPatName = null,reservedDoctorName=null;
        try {
            Map<String,Object> rowMap   = jdbc.queryForMap(querySql);  //已预约患者姓名。
            reservedPatName = rowMap.get("user_name").toString();
            reservedDoctorName  = rowMap.get("account_id_ref").toString();
        } catch (EmptyResultDataAccessException erdae)  {
            idle    = true;
        }
        if (idle || StringUtils.isBlank(reservedPatName))  {
            //有空闲。
            logger.info("++++makeMachineReservation() updateSql:"+updateSql);
            int updRet  = jdbc.update(updateSql);
            if (1 == updRet)    {
                //下约成功了。
                retMap.put("success","yes");
                retMap.put("msg","患者宝宝【"+patientName+"】已预约成功了。");
            } else {
                //下约失败了。
                retMap.put("success","no");
                retMap.put("msg","由于数据库操作失败，患者宝宝【"+patientName+"】预约失败了！！");
            }
        } else {
            //已被预约掉了。
            retMap.put("success","no");
            retMap.put("msg","该时段已被医生【"+reservedDoctorName+"】预约了患者【"+reservedPatName+"】！！");
        }

        return retMap;
    }
    @Override
    public Map makeMachineReservation2(final Integer machineId, final String cureDate, final String cureTime, final String patientId, final String patientName, final String accountId)    {
        Map<String,Object> retMap   = new HashMap<>();
        //查询该空闲预约是否已被预约掉了。
        String cureBegintime="",cureEndtime="";
        int idx = cureTime.indexOf('-');
        if (idx>1)  {
            cureBegintime   = cureTime.substring(0, idx);
            cureEndtime     = cureTime.substring(idx+1);
        } else {
            //cureTime格式错误。
            retMap.put("success","no");
            retMap.put("msg","参数cureTime格式错误！！正确格式应该是15:30-16:00");
            return retMap;
        }
        String querySql1    = "SELECT COUNT(*) FROM rsre_reservation WHERE patient_id_ref='"+patientId+"' AND cure_date='"+cureDate+"' AND cure_begintime='"+cureBegintime+"' AND cure_endtime='"+cureEndtime+"'";
        logger.info("++++makeMachineReservation2() querySql:"+querySql1);
        Integer count   = jdbc.queryForObject(querySql1, java.lang.Integer.class);
        if (0 != count.intValue())  {
            retMap.put("success","no");
            retMap.put("msg","由于宝宝【"+patientName+"】已有该时段的预约（会造成时间冲突），所以请选择其他时段进行预约！！");
            return retMap;
        }

        String querySql2    = "SELECT a.id, a.patient_id_ref, a.account_id_ref FROM rsre_reservation a " +
                "WHERE a.prj_id_ref="+machineId.toString()+" AND a.cure_date='"+cureDate+"' AND a.cure_begintime='"+cureBegintime+"' AND a.cure_endtime='"+cureEndtime+"'";
        logger.info("++++makeMachineReservation2() querySql:"+querySql2);
        boolean idle    = false;
        Object reservedPatName = null,reservedDoctorName = null;
        Integer reservationId   = 0;
        try {
            Map<String,Object> rowMap   = jdbc.queryForMap(querySql2);  //已预约患者。
            reservationId   = (Integer) rowMap.get("id");
            reservedPatName = rowMap.get("patient_id_ref");
            reservedDoctorName  = rowMap.get("account_id_ref");
        } catch (EmptyResultDataAccessException erdae)  {
            //该时段的这台治疗仪是空闲的。
            idle    = true;
        }
        if (idle || null == reservedPatName)  {
            //有空闲。
            final String updateSql  = "UPDATE rsre_reservation SET patient_id_ref='" + patientId.toString() +"', account_id_ref='" + accountId.toString() + "' WHERE id="+reservationId.toString();
            logger.info("++++makeMachineReservation2() updateSql:"+updateSql);
            int updRet  = jdbc.update(updateSql);
            if (1 == updRet)    {
                //下约成功了。
                retMap.put("success","yes");
                retMap.put("msg","患者宝宝【"+patientName+"】已预约成功了。");
            } else {
                //下约失败了。
                retMap.put("success","no");
                retMap.put("msg","由于数据库操作失败，患者宝宝【"+patientName+"】预约失败了！！");
            }
        } else {
            //已被预约掉了。
            retMap.put("success","no");
            retMap.put("msg","该时段已被医生【"+reservedDoctorName+"】预约了患者【"+reservedPatName+"】！！");
        }

        return retMap;
    }

    /**
     * 取消预约。
     * @param reservationId 预约ID。
     * @param doctorName 取消人ID（登录ID）。
     * @return
     */
    @Override
    public Map cancelMachineReservation(final Integer reservationId, final String doctorName) {
        Map<String,Object> retMap   = new HashMap<>();
        final String updateSql  = "UPDATE rsre_reservation SET patient_id_ref=NULL WHERE id="+reservationId.toString();
        int updRet  = jdbc.update(updateSql);
        if (1 == updRet)    {
            retMap.put("success","yes");
            retMap.put("msg","该预约被正常取消了。");
        } else {
            retMap.put("success","no");
            retMap.put("msg","由于操作数据异常，导致该预约不能被取消！！");
        }

        return retMap;
    }
    @Override
    public Map cancelMachineReservation2(final Integer machineId, final String cureDate, final String cureTime, final String doctorName) {
        Map<String,Object> retMap   = new HashMap<>();
        //从参数cureTime(16:00-16:30)中解析出cureBegintime,cureEndtime.
        String cureBegintime="",cureEndtime="";
        int idx = cureTime.indexOf('-');
        cureBegintime   = cureTime.substring(0, idx);
        cureEndtime     = cureTime.substring(idx+1);
        //从参数cureDate(2023-04-19 星期三)中解析出cureDate。
        final String sqlCureDate= cureDate.substring(0,"2023-04-19".length());
        final String updateSql  = "UPDATE rsre_reservation SET patient_id_ref=NULL, account_id_ref='"+doctorName+"' WHERE prj_id_ref="+machineId.toString()+" AND cure_date='"+sqlCureDate+"' AND cure_begintime='"+cureBegintime+"' AND cure_endtime='"+cureEndtime+"'";
        logger.info("++++cancelMachineReservation2() updateSql:"+updateSql);
        int updRet  = jdbc.update(updateSql);
        if (1 == updRet)    {
            retMap.put("success","yes");
            retMap.put("msg","该预约被正常取消了。");
        } else {
            retMap.put("success","no");
            retMap.put("msg","由于操作数据异常，导致该预约不能被取消！！");
        }

        return retMap;
    }

    /**
     * 查询某个患者的所有预约。
     * @param patientId
     */
    @Override
    public List<Reservation> queryReservationByPat(final String patientId) {
        String querySql = "SELECT a.id, a.cure_date, b.project_name, DATE_FORMAT(a.cure_begintime,'%H:%i') AS begintime, DATE_FORMAT(a.cure_endtime,'%H:%i') AS endtime, a.account_id_ref " +
                "FROM rsre_reservation a LEFT JOIN rsre_project b ON a.prj_id_ref=b.id " +
                "WHERE patient_id_ref='" + patientId + "' " +
                "ORDER BY a.cure_date DESC";
        List<Reservation> retList  = new ArrayList();
        logger.info("++++queryReservationByPat() querySql:"+querySql);
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        for(Map<String,Object> rowMap : rowList)   {
            Reservation rese    = new Reservation();
            rese.setId((Integer)rowMap.get("id"));
            rese.setDate(rowMap.get("cure_date").toString());
            rese.setPrjName(rowMap.get("project_name").toString());
            rese.setStartTime(rowMap.get("begintime").toString());
            rese.setEndTime(rowMap.get("endtime").toString());
            rese.setOperatorName(rowMap.get("account_id_ref").toString());
            retList.add(rese);
        }

        return retList;
    }

    /**
     * 查询治疗仪预约表格。
     * @return
     */
    @Override
    public Map<String,Object> queryMachineReseTable()    {
        //查询可预约的日期。
        final String queryReseDate  = "SELECT CONCAT(a.date, ' ', a.xqj) AS reservation_date " +
                "FROM (" +
                "SELECT DISTINCT(DATE_FORMAT(cure_date,'%Y-%m-%d')) AS date, " +
                "CASE " +
                "WHEN DAYOFWEEK(cure_date)=1 THEN '星期日' " +
                "WHEN DAYOFWEEK(cure_date)=2 THEN '星期一' " +
                "WHEN DAYOFWEEK(cure_date)=3 THEN '星期二' " +
                "WHEN DAYOFWEEK(cure_date)=4 THEN '星期三' " +
                "WHEN DAYOFWEEK(cure_date)=5 THEN '星期四' " +
                "WHEN DAYOFWEEK(cure_date)=6 THEN '星期五' " +
                "WHEN DAYOFWEEK(cure_date)=7 THEN '星期六' " +
                "END AS 'xqj' " +
                "FROM rsre_reservation WHERE cure_date>now() ORDER BY cure_date ASC LIMIT 0,14) AS a";

        Map<String,Object> retMap   = new HashMap<>();
        List<Map<String,Object>> rowList    = jdbc.queryForList(queryReseDate);
        retMap.put("可预约的日期", rowList);

        /*
        List<String> durationList   = new ArrayList<>();
        //上午有8个时段。
        durationList.add("08:00-08:30");
        durationList.add("08:30-09:00");
        durationList.add("09:00-09:30");
        durationList.add("09:30-10:00");
        durationList.add("10:00-10:30");
        durationList.add("10:30-11:00");
        durationList.add("11:00-11:30");
        durationList.add("11:30-12:00");
        //下午有6个时段。
        durationList.add("14:00-14:30");
        durationList.add("14:30-15:00");
        durationList.add("15:00-15:30");
        durationList.add("15:30-16:00");
        durationList.add("16:00-16:30");
        durationList.add("16:30-17:00");
        */
        retMap.put("可预约的时段", this.machineDurationList);

        List<Map<Integer,String>> machines    = new ArrayList<>();
        Map machine = new HashMap();
        machine.put("id", 6);
        machine.put("name", "脑循环");
        machines.add(machine);
        machine = new HashMap();
        machine.put("id", 7);
        machine.put("name", "神肌电");
        machines.add(machine);
        machine = new HashMap();
        machine.put("id", 8);
        machine.put("name", "痉挛肌");
        machines.add(machine);
        retMap.put("可预约的治疗仪", machines);

        return retMap;
    }

    /**
     * 查询某个治疗仪下已有的预约。
     * @param machineId
     */
    @Override
    public List<Map<String,Object>> queryMachineReserved(final Integer machineId)  {
        final String querySql   = "SELECT c.patient_id_ref, c.user_name, CONCAT(c.cure_date, ' ', c.xqj) AS cure_date, c.cure_time " +
                "FROM ( SELECT a.patient_id_ref, b.user_name, a.cure_date, CONCAT(DATE_FORMAT(a.cure_begintime,'%H:%i'), '-' , DATE_FORMAT(a.cure_endtime,'%H:%i')) AS cure_time, " +
                "CASE " +
                "WHEN DAYOFWEEK(cure_date)=1 THEN '星期日' " +
                "WHEN DAYOFWEEK(cure_date)=2 THEN '星期一' " +
                "WHEN DAYOFWEEK(cure_date)=3 THEN '星期二' " +
                "WHEN DAYOFWEEK(cure_date)=4 THEN '星期三' " +
                "WHEN DAYOFWEEK(cure_date)=5 THEN '星期四' " +
                "WHEN DAYOFWEEK(cure_date)=6 THEN '星期五' " +
                "WHEN DAYOFWEEK(cure_date)=7 THEN '星期六' " +
                "END AS 'xqj' " +
                "FROM rsre_reservation a LEFT JOIN pt_user_info b ON b.id=a.patient_id_ref " +
                "WHERE a.prj_id_ref=" + machineId.toString() + " AND a.patient_id_ref!='' AND a.cure_date>now() AND b.tenant_id!='0') AS c";
        logger.info("++++queryMachineReserved() querySql:"+querySql);
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        return rowList;
    }

    /**
     * 查询某台治疗仪的今日预约。
     * @param machineId
     * @return
     */
    @Override
    public List<Map<String,Object>> queryMachineReservedToday(final Integer machineId)  {
        final String querySql   = "SELECT CONCAT(DATE_FORMAT(a.cure_begintime,'%H:%i'), '-', DATE_FORMAT(a.cure_endtime,'%H:%i')) AS cure_time, b.user_name " +
                "FROM rsre_reservation a LEFT JOIN " +
                "(SELECT id, user_name FROM pt_user_info WHERE tenant_id!='0') AS b " +
                "ON b.id=a.patient_id_ref " +
                "WHERE a.cure_date=CURDATE() AND prj_id_ref=" + machineId.toString() + " ORDER BY a.cure_begintime ASC";
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        return rowList;
    }

/*---------------------------------------------------------------------------*/
    /**
     * 查询治疗师的排班。
     * @param auAccountUsername 登录手机号（工号）。
     */
    @Override
    public List queryCurerSchedule(final String auAccountUsername)    {
        try {
            Date date = new Date();
            String dateStr = DateUtil.getDate_8(date);
            final String querySql   = "SELECT c.id, CONCAT(c.work_date,' ', xqj) AS work_date, c.am, c.pm " +
                    "FROM (SELECT a.id, DATE_FORMAT(a.work_date,'%Y-%m-%d') AS work_date, a.am, a.pm, " +
                    "CASE " +
                    "WHEN DAYOFWEEK(a.work_date)=1 THEN '星期日' " +
                    "WHEN DAYOFWEEK(a.work_date)=2 THEN '星期一' " +
                    "WHEN DAYOFWEEK(a.work_date)=3 THEN '星期二' " +
                    "WHEN DAYOFWEEK(a.work_date)=4 THEN '星期三' " +
                    "WHEN DAYOFWEEK(a.work_date)=5 THEN '星期四' " +
                    "WHEN DAYOFWEEK(a.work_date)=6 THEN '星期五' " +
                    "WHEN DAYOFWEEK(a.work_date)=7 THEN '星期六' " +
                    "END AS 'xqj' FROM rsre_curer_schedule a LEFT JOIN rsre_curer b ON a.curer_id_ref=b.id WHERE b.au_account_username='"+auAccountUsername+"' AND a.work_date>'"+dateStr+"') AS c";
            logger.info("++++queryCurerSchedule() querySql:"+querySql);
            return jdbc.queryForList(querySql);
        } catch (Exception ex) {
            logger.error(ex);
        }
        return new ArrayList();
    }

    /**
     * 设定某天上/下午为上班。
     * @param scheduleId
     * @param ap 上午或下午，a上午p下午。
     */
    public Map setWorkday(final Integer scheduleId, final String ap)    {
        final String updateSql  = "UPDATE rsre_curer_schedule SET " + (ap.equalsIgnoreCase("am")?"am":"pm") + "='Y' WHERE id=" + scheduleId.toString();
        logger.info("++++setWorkday() updateSql:"+updateSql);
        final int updRet  = jdbc.update(updateSql);
        Map<String,Object> retMap   = new HashMap<>();
        if (1 == updRet)    {
            retMap.put("success","yes");
            retMap.put("msg","已成功设定为上班。");
        } else {
            retMap.put("success","no");
            retMap.put("msg","由于操作数据异常，导致设定上班失败了！！");
        }
        return retMap;
    }

    /**
     * 设定某天上/下午为休假。
     * @param scheduleId
     * @param ap
     * @return
     */
    public Map setHoliday(final Integer scheduleId, final String ap)    {
        final String updateSql  = "UPDATE rsre_curer_schedule SET " + (ap.equalsIgnoreCase("am")?"am":"pm") + "='N' WHERE id=" + scheduleId.toString();
        logger.info("++++setHoliday() updateSql:"+updateSql);
        final int updRet  = jdbc.update(updateSql);
        Map<String,Object> retMap   = new HashMap<>();
        if (1 == updRet)    {
            retMap.put("success","yes");
            retMap.put("msg","已成功设定为休假。");
        } else {
            retMap.put("success","no");
            retMap.put("msg","由于操作数据异常，导致设定休假失败了！！");
        }
        return retMap;
    }

    /**
     * 查询治疗师列表。
     * @param prjId 治疗项目。
     * @param cureDate 治疗日期。
     * @return 上班的治疗师列表。
     */
    @Override
    public List<Map<String,Object>> queryCurerList(final Integer prjId, final String cureDate)  {
        final String querySql   = "SELECT c.curer_id_ref AS curer_id, c.name AS curer_name " +
                "FROM (SELECT a.curer_id_ref, b.name FROM rsre_curer_prj a LEFT JOIN rsre_curer b ON a.curer_id_ref=b.id WHERE b.enable='Y' AND a.prj_id_ref="+prjId.toString()+
                ") AS c LEFT JOIN rsre_curer_schedule d ON d.curer_id_ref=c.curer_id_ref WHERE d.work_date='"+cureDate+"' AND (d.am='Y' OR d.pm='Y')";
        logger.info("queryCurerList() querySql:"+querySql);
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        return rowList;
    }

    /**
     * 查询治疗师的项目空闲时段。
     * @param cureDate
     * @param curerId
     * @param prjId
     */
    @Override
    public Map queryCurerIdle(final String cureDate, final Integer curerId, final Integer prjId)    {
        String am,pm,reservedBegintime;
        Map<String,Object> retMap   = new HashMap<>();
        //查询治疗师的空闲时段。
        final String queryWorkday   = "SELECT * FROM rsre_curer_schedule WHERE curer_id_ref="+curerId.toString()+" AND work_date='"+cureDate+"'";
        //查询治疗师该日的已预约项目。
        final String queryCurerReservation  = "SELECT DATE_FORMAT(cure_begintime,'%H:%i') AS begintime FROM rsre_reservation WHERE curer_id_ref="+curerId.toString()+" AND cure_date='"+cureDate+"' AND prj_id_ref="+prjId.toString()+" AND patient_id_ref!=''";
        Map<String,Object> workday  = jdbc.queryForMap(queryWorkday);   //查询治疗师这天的排班。
        if (null == workday || 0 == workday.size()) {
            retMap.put("msg","治疗师"+curerId.toString()+"今天休假！！");
        } else {
            am  = (String)workday.get("am");
            pm  = (String)workday.get("pm");
            if ((StringUtils.isNotBlank(am) && am.equalsIgnoreCase("y")) ||
                    (StringUtils.isNotBlank(pm) && pm.equalsIgnoreCase("y")))   {
                //上班的。
                List<Map<String,Object>> curerReservations  = jdbc.queryForList(queryCurerReservation);
                Map<String,String> idleDurMap   = new HashMap<>();
                if (am.equalsIgnoreCase("y")) {
                    idleDurMap.putAll(prj2AmMap);
                }
                if (pm.equalsIgnoreCase("y")) {
                    idleDurMap.putAll(prj2PmMap);
                }
                for(Map<String,Object> reservation : curerReservations)   {
                    reservedBegintime   = reservation.get("begintime").toString();
                    idleDurMap.remove(reservedBegintime);
                }   //idleDurMap中剩余的时段就是空闲时段了。
                List<String> idleDurList    = new ArrayList<>();
                idleDurList.addAll(idleDurMap.keySet());
                Collections.sort(idleDurList);
                retMap.put("idle", idleDurList);
            }
        }

        return retMap;
    }

    /**
     * 查询治疗师的已预约。
     * @param curerId
     * @return
     * TODO 返回：可预约日期列表、可预约项目列表。
     */
/*    public List queryCurerReserved(final Integer curerId)    {
        String cureDate,cureDataStr,cureTime,am,pm,querySql3;
        //查询治疗项目。
        final String querySql1  = "SELECT * FROM rsre_curer_prj WHERE curer_id_ref=" + curerId.toString();
        //查询明天开始的排班和该日的预约。
        List<DailyReservation> dayReservedList  = new ArrayList<>();
        final String querySql2  = "SELECT c.work_date, CONCAT(c.date_str, ' ', xqj) AS long_workdate, am, pm FROM(SELECT work_date, DATE_FORMAT(work_date,'%Y-%m-%d') AS date_str, " +
                "CASE " +
                "WHEN DAYOFWEEK(work_date)=1 THEN '星期日' " +
                "WHEN DAYOFWEEK(work_date)=2 THEN '星期一' " +
                "WHEN DAYOFWEEK(work_date)=3 THEN '星期二' " +
                "WHEN DAYOFWEEK(work_date)=4 THEN '星期三' " +
                "WHEN DAYOFWEEK(work_date)=5 THEN '星期四' " +
                "WHEN DAYOFWEEK(work_date)=6 THEN '星期五' " +
                "WHEN DAYOFWEEK(work_date)=7 THEN '星期六' " +
                "END AS 'xqj', am, pm FROM rsre_curer_schedule WHERE curer_id_ref=3 AND work_date>CURDATE() ORDER BY work_date ASC) AS c";
        List<Map<String,Object>> rowList2   = jdbc.queryForList(querySql2);
        for(Map<String,Object> rowMap2 : rowList2)   {
            cureDate    = rowMap2.get("work_date").toString();  //上班日期。
            cureDataStr = rowMap2.get("long_workdate").toString();  //"2023-04-22 星期六"。
            am  = rowMap2.get("am").toString();
            pm  = rowMap2.get("pm").toString();
            DailyReservation dr = new DailyReservation();
            dr.setDate(cureDate);
            if (am.equalsIgnoreCase("y") || pm.equalsIgnoreCase("y"))   {
                //查询该日的已预约。
                querySql3   = "SELECT * FROM rsre_reservation WHERE curer_id_ref="+curerId.toString()+" AND cure_date='"+cureDate+"'";
                List<Map<String, Object>> rowList3 = jdbc.queryForList(querySql3);
                if (!rowList3.isEmpty())    {
                    //组装该日的预约。
                }
            } else {

            }
            dayReservedList.add(dr);
        }

        return dayReservedList;
    }
*/


    /**
     * 下约治疗师。
     * 每次下约就INSERT一条记录。
     * @param prjId 治疗项目。
     * @param curerId
     * @param cureDate
     * @param cureTime
     * @param patientId
     * @param patientName
     * @param accountId
     * @return
     */
    @Override
    public Map makeCurerReservation(final Integer prjId, final Integer curerId, final String cureDate, final String cureTime, final String patientId, final String patientName, final String accountId)    {
        String cureBegintime="",cureEndtime="";
        Map retMap  = new HashMap();
        int idx = cureTime.indexOf('-');
        if (idx>1)  {
            cureBegintime   = cureTime.substring(0, idx);
            cureEndtime     = cureTime.substring(idx+1);
        } else {
            //cureTime格式错误。
            retMap.put("success","no");
            retMap.put("msg","参数cureTime格式错误！！正确格式应该是15:30-16:00");
            return retMap;
        }

        //查询治疗师该时段是否已被预约了。
        String querySql = "SELECT * FROM rsre_reservation WHERE curer_id_ref="+curerId.toString()+" AND prj_id_ref="+prjId.toString()+" AND cure_date='"+cureDate+"' AND cure_begintime='"+cureBegintime+"' AND cure_endtime='"+cureEndtime+"'";
        List<Map<String,Object>> rowList    = jdbc.queryForList(querySql);
        if (!rowList.isEmpty())  {
            String reservedPatId    = rowList.get(0).get("patient_id_ref").toString();
            retMap.put("success","no");
            retMap.put("msg","该时段"+cureTime+"已被患者"+reservedPatId+"预约掉了！！");
            return retMap;
        }

        //下约。
        final String insertSql  = "INSERT INTO rsre_reservation (patient_id_ref,curer_id_ref,prj_id_ref,cure_date,cure_begintime,cure_endtime,account_id_ref,make_res_timestamp) VALUES (?,?,?,?,?,?,?,NOW())";
        int insRet  = 0;
        try {
            insRet = jdbc.update(insertSql,
                    patientId,
                    curerId,
                    prjId,
                    cureDate,
                    cureBegintime,
                    cureEndtime,
                    accountId
            );
        } catch (DataAccessException dae)   {
            logger.error(dae);
        }
        if (1 == insRet)    {
            retMap.put("success","yes");
            retMap.put("msg","已预约成功。");
        } else {
            retMap.put("success","no");
            retMap.put("msg","由于操作数据库异常，导致预约失败！！");
        }
        return retMap;
    }

    /**
     * 取消治疗师的预约。
     * 不删除记录（也不INSERT记录），仅设置patient_id_ref=''。
     * @param reservationId
     */
    @Override
    public Map cancelCurerReservation(final Integer reservationId, final String operatorDoctor)    {
        Map<String,Object> retMap   = new HashMap<>();
        final String updateSql  = "UPDATE rsre_reservation SET patient_id_ref='', cancel_account='"+operatorDoctor+"', cancel_res_timestamp=NOW() WHERE id="+reservationId.toString();
        int updRet  = 0;
        try {
            updRet = jdbc.update(updateSql);
        } catch (DataAccessException dae)   {
            logger.error(dae);
        }
        if (1 == updRet)    {
            retMap.put("success", "yes");
            retMap.put("msg", "预约已被成功取消了。");
        } else {
            retMap.put("success", "no");
            retMap.put("msg", "由于操作数据库异常，导致预约无法取消！！");
        }
        return retMap;
    }
}
